const sql = require('mssql');
const baseFn = require('../../public/base.js');

/**
 * @description 购退
 */



// 查询入库单明细
const getPurchaseRetreatOrderDetail = async (ctx) => {
    try {
    const { index, pageSize, params: { times, RKID, GYSBM, BMBM, SPXX } } = ctx.request.body;
    let publicSql = ` FROM RKD_MX RM LEFT JOIN 
        RKD_ZB RZ ON RM.ID = RZ.ID LEFT JOIN KC_KCDTB KC ON RM.YPBM = KC.YPBM AND RM.SCPH = KC.SCPH AND RM.CKBM = KC.CKBM AND RZ.BMBM = KC.BMBM
        LEFT JOIN BM_YP Y ON RM.YPBM = Y.BM LEFT JOIN (SELECT COUNT(TM.SL) AS THSL,TM.ID_RKD,TM.YPBM,TM.SCPH FROM THD_MX TM GROUP BY TM.ID_RKD,
        TM.YPBM,TM.SCPH) TH ON RM.ID = TH.ID_RKD AND RM.YPBM = TH.YPBM AND RM.SCPH = TH.SCPH WHERE RZ.SH = 'T' AND RZ.BMBM = '${BMBM}' `;

    let selectSql = `SELECT * FROM  ( SELECT ROW_NUMBER() OVER(ORDER BY RM.YPBM) as px, RM.YPBM,RM.YPTM,RM.YPMC,RM.YPGG,RM.JLDW,RM.JXMC,RM.SCCJ,RM.PZWH,RM.CDMC,RM.DJ,RM.JE,RM.YJ,RM.YJ_HS,RM.MJSL,RM.SL,Y.YPLX,
        RM.DJ_HS,RM.SCPH,RM.HWBH,RM.SCRQ,RM.YXQZ,RM.YXQX,RM.CKBM,RM.CKMC,KC.KCSL,RZ.ID,RZ.RQ,RZ.JSFSMC,RZ.ZSL,RZ.ZJE,(RM.SL - ISNULL(TH.THSL,0) ) AS SYSL,Y.SSXK ${publicSql} `;
    let totalSql = ` SELECT COUNT(RM.YPBM) AS total ${publicSql} `;
    let actionSqlList = [];
    if (0 in times) {
        actionSqlList.push(` AND RZ.RQ >= '${times[0]}' AND RZ.RQ <= '${times[1]}' `);
        // publicSql += ` AND RZ.RQ >= '${times[0]}' AND RZ.RQ <= '${times[1]}' `;
    }
    if (RKID) {
        actionSqlList.push(` AND RZ.ID = '${RKID}' `);
        // publicSql += ` AND RZ.ID = '${RKID}' `;
    }
    if (GYSBM) {
        actionSqlList.push(` AND RZ.DWBM = '${GYSBM}' `);
        // publicSql += ` AND RZ.DWBM = '${GYSBM}' `;
    }
    if (SPXX) {
        actionSqlList.push(` AND (RM.YPBM LIKE '%${SPXX}%' OR Y.MC LIKE '%${SPXX}%' OR Y.JP LIKE '%${SPXX}%'  ) `);
        // publicSql += ` AND (RM.YPBM LIKE '%${SPXX}%' OR Y.MC LIKE '%${SPXX}%' OR Y.JP LIKE '%${SPXX}%'  ) `;
    }
    // selectSql += publicSql;
    totalSql += actionSqlList.join(' ');
    selectSql += ` ${actionSqlList.join(' ')} ) t1 WHERE T1.px between ${(index - 1) * pageSize + 1} and ${index * pageSize} `;
    let totalResult = await sql.query(totalSql);
    let selectResult = await sql.query(selectSql);
    if (selectResult.rowsAffected[0] > 0) {
        ctx.body = { code: 200, message: '获取数据成功', data: { total: totalResult.recordset[0].total, result: selectResult.recordset } }
    } else {
        ctx.body = { code: 400, message: '没有获取到数据' }
    }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}

// 保存购退单
const keepPurchaseRetreatInfo = async (ctx) => {
    try {
        const { searchList, shopList } = ctx.request.body;
        let thdId = await baseFn.createId(searchList.userId, 'THD_ZB');
        let actionSqlList = [];

        // 插入购退明细表
        let insertThdMxSql = ` INSERT INTO [dbo].[THD_MX]([ID], [XH], [NB], [YPBM], [YPTM], [YPMC], [YPGG], [JXMC], [SCCJ], [CDMC], [PZWH], [SCPH],
        [SCRQ], [YXQX], [YXQZ], [JLDW], [MJSL], [SL], [JS], [SS], [YJ], [YJ_HS], [ZK], [DJ], [DJ_HS], [JE], [SE], [HJ],  [CKBM], 
        [CKMC], [HWBH], [CB], [JG], [FHRQ], [BZQK], [ZLZK], [FHJL],[GTYY],[ID_RKD]) `;

        let zje = 0, zss = 0, zjs = 0, zsl = 0;

        shopList.forEach((shop, ind) => {

            let dj = Number(shop.DJ ? shop.DJ : 0);
            let gtsl = Number(shop.GTSL ? shop.GTSL : 0);
            let mjsl = Number(shop.MJSL ? shop.MJSL : 0);
            let je = dj * gtsl;
            let js = mjsl === 0 ? 0 : Math.floor(gtsl / mjsl);
            let ss = gtsl - (mjsl * js);
            zje += je;
            zss += ss;
            zjs += js;
            zsl += gtsl;
            let xhSerialNum = '00000'.slice(0, 5 - (ind + 1).toString().length) + (ind + 1);

            insertThdMxSql += ` SELECT '${thdId}', ${baseFn.getNowTimeId().msDate + xhSerialNum},${ind + 1} ,'${shop.YPBM}','${shop.YPTM}','${shop.YPMC}',
            '${shop.YPGG}','${shop.JXMC}','${shop.SCCJ}','${shop.CDMC}','${shop.PZWH}','${shop.SCPH}','${shop.SCRQ}',${shop.YXQX},'${shop.YXQZ}','${shop.JLDW}',
            ${mjsl},${gtsl},${js},${ss},${shop.YJ},${shop.YJ_HS},100,${dj},${shop.DJ_HS},${je},0,${je},'${shop.CKBM}','${shop.CKMC}','${shop.HWBH}','A',
            'F','${baseFn.getNowTimeId().fullDate}','${shop.BZQK}','${shop.ZLZK}','${shop.FHJL}','${shop.THYY}','${shop.ID}'`;
            if (ind != shopList.length - 1) {
                insertThdMxSql += ` UNION `;
            }
        });
        actionSqlList.push(insertThdMxSql);

        // 插入购退结算表
        let payInfoResult = await sql.query(`SELECT MC,KM1BM,KMBM,KMQC FROM BM_JSFS WHERE BM = '${searchList.JSFS}' AND QY_FLAG = 'T'`);
        let payInfo = payInfoResult.recordset[0];
        let insertThdJsSql = `INSERT INTO [dbo].[THD_JS]([ID], [XH], [BM], [MC], [KM1BM], [KMBM], [KMQC], [JSJE]) 
        VALUES ('${thdId}', 1, '${searchList.JSFS}', '${payInfo.MC}', '${payInfo.KM1BM}', '${payInfo.KMBM}', '${payInfo.KMQC}', ${zje});`;

        actionSqlList.push(insertThdJsSql);

        // 插入购退单总表
        let personInfoResult = await sql.query(`SELECT MC FROM BM_YG WHERE BM = '${searchList.GTRBM}'`);
        let personInfo = personInfoResult.recordset[0];
        let insertThdZbSql = `INSERT INTO [dbo].[THD_ZB]([ID], [RQ], [DWBM], [DWMC], [BMBM], [BMMC], [JBRBM], [JBRMC], [JSFSBM], [JSFSMC], [ZDR], 
        [SHR], [ZDZK], [SKQX], [SKQZ], [PJLX], [PJHM], [ZSL], [ZJS], [ZSS], [ZJE], [ZSE], [ZHJ], [YJS],  [EJS], [WJS], [ZZK], [BZ], [SH] ) 
        VALUES ('${thdId}', '${baseFn.getNowTimeId().fullDate}', '${searchList.GYSBM}', '${searchList.GYSMC}', '${searchList.BMBM}', '${searchList.BMMC}', 
        '${searchList.GTRBM}','${searchList.GTRMC}', '${searchList.JSFS}', '${payInfo.MC}', '${searchList.username}', '${searchList.username}', 100, ${searchList.SKQX}, 
        '${baseFn.getDayAgo(-searchList.SKQX)}', '${searchList.PJLX}', '${searchList.PJHM}', ${zsl}, .${zjs}, ${zss},
        ${zje},0, ${zje},  ${zje},0, ${zje}, 0,'${searchList.BZ}', 'F');
        `;
        actionSqlList.push(insertThdZbSql);


        let lastSql = `SET XACT_ABORT ON Begin Tran ${actionSqlList.join(' ')}  Commit Tran `;
        let lastResult = await sql.query(lastSql);
        if (lastResult.rowsAffected[0] > 0) {
            ctx.body = { success: true }
        } else {
            ctx.body = { success: false }
        }
    } catch (err) {
        ctx.body = { success: false, err }
    }
}


module.exports = {
    'POST /getPurchaseRetreatOrderDetail': getPurchaseRetreatOrderDetail,
    'POST /keepPurchaseRetreatInfo': keepPurchaseRetreatInfo,
}